﻿Imports System.Data.SqlClient

Namespace DAL

    Public Class Helper_DAL

        Shared mStrCon As String = DAL.ConfigManager.GetValue("EstudioConnectionString")
        'Shared mStrCon As String = "Integrated Security=SSPI;;Initial Catalog=Estudio;Data Source=127.0.0.1"

        Shared Function ExecuteDataset(ByVal pcommtext As String) As DataSet
            Dim mCon As New SqlConnection(mStrCon)
            Dim Ds As New DataSet
            Dim Da As New SqlDataAdapter(pcommtext, mCon)
            Try
                mCon.Open()
                Da.Fill(Ds)
                Return Ds
            Catch ex As Exception
                Throw ex
                Return Nothing
            Finally
                mCon.Close()
                mCon.Dispose()
            End Try
        End Function

        Shared Sub ExecuteNonQuery(ByVal pcommtext As String)
            Dim mCon As New SqlConnection(mStrCon)
            Dim mComm As New SqlCommand(pcommtext, mCon)
            Try
                mCon.Open()
                mComm.ExecuteNonQuery()
            Catch ex As Exception
                Throw ex
            Finally
                mCon.Close()
                mCon.Dispose()
            End Try
        End Sub

        Shared Function ExecuteScalar(ByVal pcommtext As String) As Integer
            Dim mCon As New SqlConnection(mStrCon)
            Dim mComm As New SqlCommand(pcommtext, mCon)
            Try
                mCon.Open()
                Return mComm.ExecuteScalar
            Catch ex As Exception
                Throw ex
                Return Nothing
            Finally
                mCon.Close()
                mCon.Dispose()
            End Try
        End Function

        Shared Function ObtenerProximoId(pTabla As String) As Integer
            Dim mNuevoID As Integer = 0
            Dim mCon As New SqlConnection(mStrCon)
            Dim pcommtext As String = "SELECT ISNULL(MAX(numero),0) FROM numeros WHERE tabla = '" & pTabla & "'"
            Dim mComm As New SqlCommand(pcommtext, mCon)

            Try
                mCon.Open()
                mNuevoID = mComm.ExecuteScalar()
                mNuevoID = mNuevoID + 1
                ExecuteNonQuery("INSERT INTO numeros (numero,tabla) VALUES (" & mNuevoID & ",'" & pTabla & "')")
                Return mNuevoID
            Catch ex As Exception
                Throw ex
                Return Nothing
            Finally
                mCon.Close()
                mCon.Dispose()
            End Try
        End Function


        Shared Function CalcularDVV(ptabla) As Integer
            Dim DVV As Integer = 0
            Dim comm As String
            'calculamos el dvv de la tabla.
            comm = "SELECT ISNULL(SUM(dvh),0) FROM " & ptabla
            Try
                DVV = Helper_DAL.ExecuteScalar(comm)
            Catch ex As Exception
                Throw ex
            End Try
            Return DVV
        End Function


        Shared Sub setearDVV(ByVal ptabla As String, ByVal pdvv As Integer)
            Dim comm As String = ""
            'actualizamos el dvv de la tabla correspondiente en la tavla DVV (todas las tablas con dvv).
            comm = "UPDATE DVV SET valor=" & pdvv & " WHERE tabla='" & ptabla & "'"
            Try
                Helper_DAL.ExecuteNonQuery(comm)
            Catch ex As Exception
                Throw ex
            End Try
        End Sub


        Shared Function obtenerDVV(ByVal ptabla As String)
            Dim DVV As Integer = 0
            Dim comm As String = ""
            'obtenemos el dvv de la tabla correspondiente.
            comm = "SELECT valor FROM DVV WHERE tabla='" & ptabla & "'"
            Try
                DVV = Helper_DAL.ExecuteScalar(comm)
            Catch ex As Exception
                Throw ex
            End Try
            Return DVV
        End Function



    End Class

End Namespace